--
-- PostgreSQL database dump
--

-- Started on 2012-12-13 09:14:23

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

--
-- TOC entry 1812 (class 1262 OID 43033)
-- Name: prototipo; Type: DATABASE; Schema: -; Owner: postgres
--

CREATE DATABASE prototipo WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'Spanish_Bolivia.1252' LC_CTYPE = 'Spanish_Bolivia.1252';


ALTER DATABASE prototipo OWNER TO postgres;

\connect prototipo

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

--
-- TOC entry 318 (class 2612 OID 16386)
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: postgres
--

--CREATE PROCEDURAL LANGUAGE plpgsql;


ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO postgres;

SET search_path = public, pg_catalog;

--
-- TOC entry 19 (class 1255 OID 43135)
-- Dependencies: 318 6
-- Name: actualizarusuario(integer, text, text, text, integer, bytea, text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION actualizarusuario(integer, text, text, text, integer, bytea, text) RETURNS void
    LANGUAGE plpgsql
    AS $_$declare
begin 
    update persona set nombre=$2,apellido=$3,correo=$4,id_pais=$5,imagen=$6 where id_persona=$1;
    update sesion set username=$7 where id_persona=$1;
return;
end;$_$;


ALTER FUNCTION public.actualizarusuario(integer, text, text, text, integer, bytea, text) OWNER TO postgres;

--
-- TOC entry 21 (class 1255 OID 43159)
-- Dependencies: 318 6
-- Name: addsugerencia(integer, text, character); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION addsugerencia(integer, text, character) RETURNS void
    LANGUAGE plpgsql
    AS $_$declare
    
begin
    insert into comentario (fecha,comenta,id_persona)values(now(),$2,$1);
    if $3='B' then
     update persona set habilitado='false'where id_persona=$1;
    end if;
    
    return;
         
 end;$_$;


ALTER FUNCTION public.addsugerencia(integer, text, character) OWNER TO postgres;

--
-- TOC entry 20 (class 1255 OID 43133)
-- Dependencies: 6 318
-- Name: insertardatos(text, text, text, integer, text, text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION insertardatos(text, text, text, integer, text, text) RETURNS void
    LANGUAGE plpgsql
    AS $_$declare
   id bigint;
begin 
   insert into persona(nombre,apellido,correo,id_pais,habilitado)values($1,$2,$3,$4,true);
   id :=lastVal();
   insert into sesion values(id,$5,$6);
   return;
end;
  $_$;


ALTER FUNCTION public.insertardatos(text, text, text, integer, text, text) OWNER TO postgres;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- TOC entry 1511 (class 1259 OID 43141)
-- Dependencies: 6
-- Name: comentario; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE comentario (
    id_comentario integer NOT NULL,
    fecha date NOT NULL,
    comenta text NOT NULL,
    id_persona integer NOT NULL
);


ALTER TABLE public.comentario OWNER TO postgres;

--
-- TOC entry 1510 (class 1259 OID 43139)
-- Dependencies: 6 1511
-- Name: comentario_id_comentario_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE comentario_id_comentario_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.comentario_id_comentario_seq OWNER TO postgres;

--
-- TOC entry 1815 (class 0 OID 0)
-- Dependencies: 1510
-- Name: comentario_id_comentario_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE comentario_id_comentario_seq OWNED BY comentario.id_comentario;


--
-- TOC entry 1505 (class 1259 OID 43062)
-- Dependencies: 6
-- Name: pais; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE pais (
    id_pais integer NOT NULL,
    nombre character varying(20) NOT NULL
);


ALTER TABLE public.pais OWNER TO postgres;

--
-- TOC entry 1506 (class 1259 OID 43065)
-- Dependencies: 6 1505
-- Name: pais_id_pais_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE pais_id_pais_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.pais_id_pais_seq OWNER TO postgres;

--
-- TOC entry 1816 (class 0 OID 0)
-- Dependencies: 1506
-- Name: pais_id_pais_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE pais_id_pais_seq OWNED BY pais.id_pais;


--
-- TOC entry 1507 (class 1259 OID 43067)
-- Dependencies: 6
-- Name: persona; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE persona (
    id_persona integer NOT NULL,
    nombre character varying(15),
    apellido character varying(15),
    correo character varying(20),
    id_pais integer NOT NULL,
    imagen bytea,
    habilitado boolean NOT NULL
);


ALTER TABLE public.persona OWNER TO postgres;

--
-- TOC entry 1508 (class 1259 OID 43073)
-- Dependencies: 6 1507
-- Name: persona_id_persona_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE persona_id_persona_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.persona_id_persona_seq OWNER TO postgres;

--
-- TOC entry 1817 (class 0 OID 0)
-- Dependencies: 1508
-- Name: persona_id_persona_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE persona_id_persona_seq OWNED BY persona.id_persona;


--
-- TOC entry 1509 (class 1259 OID 43094)
-- Dependencies: 6
-- Name: sesion; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE sesion (
    id_persona bigint NOT NULL,
    username character varying(30) NOT NULL,
    clave character varying(30) NOT NULL
);


ALTER TABLE public.sesion OWNER TO postgres;

--
-- TOC entry 1791 (class 2604 OID 43144)
-- Dependencies: 1510 1511 1511
-- Name: id_comentario; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE comentario ALTER COLUMN id_comentario SET DEFAULT nextval('comentario_id_comentario_seq'::regclass);


--
-- TOC entry 1789 (class 2604 OID 43075)
-- Dependencies: 1506 1505
-- Name: id_pais; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE pais ALTER COLUMN id_pais SET DEFAULT nextval('pais_id_pais_seq'::regclass);


--
-- TOC entry 1790 (class 2604 OID 43076)
-- Dependencies: 1508 1507
-- Name: id_persona; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE persona ALTER COLUMN id_persona SET DEFAULT nextval('persona_id_persona_seq'::regclass);


--
-- TOC entry 1795 (class 2606 OID 43107)
-- Dependencies: 1507 1507
-- Name: email; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY persona
    ADD CONSTRAINT email UNIQUE (correo);


--
-- TOC entry 1806 (class 2606 OID 43149)
-- Dependencies: 1511 1511 1511
-- Name: initi; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY comentario
    ADD CONSTRAINT initi PRIMARY KEY (id_comentario, id_persona);


--
-- TOC entry 1793 (class 2606 OID 43078)
-- Dependencies: 1505 1505
-- Name: pk; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY pais
    ADD CONSTRAINT pk PRIMARY KEY (id_pais);


--
-- TOC entry 1798 (class 2606 OID 43080)
-- Dependencies: 1507 1507
-- Name: pk_persona; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY persona
    ADD CONSTRAINT pk_persona PRIMARY KEY (id_persona);


--
-- TOC entry 1800 (class 2606 OID 43121)
-- Dependencies: 1509 1509
-- Name: seky; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY sesion
    ADD CONSTRAINT seky PRIMARY KEY (id_persona);


--
-- TOC entry 1802 (class 2606 OID 43119)
-- Dependencies: 1509 1509
-- Name: uni_per; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY sesion
    ADD CONSTRAINT uni_per UNIQUE (id_persona);


--
-- TOC entry 1804 (class 2606 OID 43105)
-- Dependencies: 1509 1509
-- Name: use; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY sesion
    ADD CONSTRAINT use UNIQUE (username);


--
-- TOC entry 1796 (class 1259 OID 43081)
-- Dependencies: 1507
-- Name: fki_fkP; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE INDEX "fki_fkP" ON persona USING btree (id_pais);


--
-- TOC entry 1807 (class 2606 OID 43082)
-- Dependencies: 1792 1505 1507
-- Name: fkP; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY persona
    ADD CONSTRAINT "fkP" FOREIGN KEY (id_pais) REFERENCES pais(id_pais) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- TOC entry 1809 (class 2606 OID 43150)
-- Dependencies: 1511 1797 1507
-- Name: foranKey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY comentario
    ADD CONSTRAINT "foranKey" FOREIGN KEY (id_persona) REFERENCES persona(id_persona) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- TOC entry 1808 (class 2606 OID 43122)
-- Dependencies: 1797 1509 1507
-- Name: per_ses; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY sesion
    ADD CONSTRAINT per_ses FOREIGN KEY (id_persona) REFERENCES persona(id_persona) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- TOC entry 1814 (class 0 OID 0)
-- Dependencies: 6
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;


-- Completed on 2012-12-13 09:14:25

--
-- PostgreSQL database dump complete
--

